﻿using System;
using System.Data.SQLite;
using AccelerateSensor.Database.SQLite;

namespace AccelerateSensor.Service.DbProxy.SQLite
{
    // ReSharper disable once IdentifierTypo
    internal class SqliteHelper
    {
        public string LastErrorMsg { get; private set; }

        public const string TableGateway = "Gateway"; //网关表
        public const string TableGatewayCommonAttribute = "GatewayCommonAttribute"; //网关公有属性表
        public const string TableGatewayPrivateAttribute = "GatewayPrivateAttribute"; //网关私有属性表
        public const string TableNode = "Node"; //节点表
        public const string TableNodeCommonAttribute = "NodeCommonAttribute"; //节点公有属性表
        public const string TableNodePrivateAttribute = "NodePrivateAttribute"; //节点私有属性表

        private static string DataBaseFile => "System.Data.dll";
        private static string ConnectionString => $"Data Source={DataBaseFile};Version=3;Password=";

        #region 初始化数据库

        public bool InitDatabase()
        {
            SqliteDbHelper.ConnectionString = ConnectionString;

            var ret = CreateDefaultDatabase();
            ret = ret && CreateTable();
            return ret;
        }

        //创建数据库
        private bool CreateDefaultDatabase()
        {
            var ret = false;
            try
            {
                using (var conn = new SQLiteConnection(ConnectionString))
                {
                    conn.Open();
                    conn.Close();
                    ret = true;
                }
            }
            catch (Exception e)
            {
                LastErrorMsg = e.Message;
            }

            return ret;
        }

        //创建表
        private bool CreateTable()
        {
            var connectionString = ConnectionString;

            //网关表
            var sql = $"create table if not exists {TableGateway}(" +
                      "Rid integer primary key autoincrement, " + //Id（自增）
                      "Uuid text not null, " + //网关编号
                      "Type integer not null, " + //网关类型
                      "LocalUpdateTime timestamp not null default (datetime('now','localtime')), " + //本地更新时间
                      "UpdateTime timestamp not null default (datetime('now','localtime')));"; //更新时间
            var ret = SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            //网关公有属性表
            sql = $"create table if not exists {TableGatewayCommonAttribute}(" +
                  "Rid integer primary key autoincrement, " + //Id（自增）
                  "GatewayUuid text not null, " + //网关编号
                  "Ip text not null, " + //网关地址
                  "TcpPort integer not null, " + //网关端口
                  "NetMask text not null, " + //
                  "NetGateway text not null, " + //
                  "StatIntervalSec integer not null);"; //
            ret = ret && SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            //网关私有属性表
            sql = $"create table if not exists {TableGatewayPrivateAttribute}(" +
                  "Rid integer primary key autoincrement, " + //Id（自增）
                  "GatewayUuid text not null, " + //网关编号
                  "StartFreq integer not null, " + //
                  "AntennaGain integer not null, " + //
                  "RfChNum integer not null, " + //
                  "ChannelDataRate text not null);"; //
            ret = ret && SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            //节点表
            sql = $"create table if not exists {TableNode}(" +
                  "Rid integer primary key autoincrement, " + //Id（自增）
                  "Uuid text not null, " + //节点编号
                  "GatewayUuid text not null, " + //网关编号
                  "Type integer not null, " + //节点类型
                  "LocalUpdateTime timestamp not null default (datetime('now','localtime')), " + //本地更新时间
                  "UpdateTime timestamp not null default (datetime('now','localtime')));"; //更新时间
            ret = ret && SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            //节点公有属性表
            sql = $"create table if not exists {TableNodeCommonAttribute}(" +
                  "Rid integer primary key autoincrement, " + //Id（自增）
                  "NodeUuid text not null, " + //节点编号
                  "SleepTimeSec integer not null, " + //
                  "SendStatusIntervalMin integer not null, " + //
                  "PowerSource integer not null, " + //
                  "WorkMode integer not null, " + //
                  "SyncEnable tinyint not null, " + //
                  "PowerVoltage real not null, " + //
                  "NodeTemp text not null, " + //
                  "PowerSaving tinyint not null);"; //
            ret = ret && SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            //节点私有属性表
            sql = $"create table if not exists {TableNodePrivateAttribute}(" +
                  "Rid integer primary key autoincrement, " + //Id（自增）
                  "NodeUuid text not null, " + //网关编号
                  "AcquireDataType integer not null, " + //
                  "DataLength integer not null, " + //
                  "SensorFreq integer not null, " + //
                  "SensorRange integer not null, " + //
                  "XAcquireEnable tinyint not null, " + //
                  "YAcquireEnable tinyint not null, " + //
                  "ZAcquireEnable tinyint not null, " + //
                  "XTrigEnable tinyint not null, " + //
                  "YTrigEnable tinyint not null, " + //
                  "ZTrigEnable tinyint not null, " + //
                  "TrigValue real not null, " + //
                  "HighPassCutoffFreq integer not null);"; //
            ret = ret && SqliteDbHelper.ExecuteNonQuery(connectionString, sql);

            if (!ret)
            {
                LastErrorMsg = "创建数据库表失败";
            }

            return ret;
        }

        #endregion
    }
}
